﻿using NPOI.OpenXml4Net.OPC;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Gl_mes.Tools
{
    internal class DataTableTools
    {
        string constr = ConfigurationManager.ConnectionStrings["MesDB"]?.ConnectionString;

        public DataTable GetDataTable(string sql)
        {
            SqlConnection conn = new SqlConnection(constr);
            try
            {
                conn.Open();
                SqlDataAdapter dap = new SqlDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                dap.Fill(dt);
                conn.Close();
                return dt;
            }
            finally { conn.Close(); }
        }

        public void ExecuteNonQuery(string sql)
        {
            SqlConnection conn = new SqlConnection(constr);
            SqlCommand comm = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
                //SqlDataAdapter dap = new SqlDataAdapter(sql, conn);
                //DataTable dt = new DataTable();
                //dap.Fill(dt);
                conn.Close();
            }
            finally { conn.Close(); }
        }

        /// <summary>
        /// 去除DATATABLE重复项
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="columnname"></param>
        public void RemoveEmpty(ref DataTable dt, string columnname)
        {
            List<DataRow> removelist = new List<DataRow>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                bool rowdataisnull = true;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (!string.IsNullOrEmpty(dt.Rows[i][columnname].ToString().Trim()))
                    {
                        rowdataisnull = false;
                    }
                }
                if (rowdataisnull)
                {
                    removelist.Add(dt.Rows[i]);
                }

            }
            for (int i = 0; i < removelist.Count; i++)
            {
                dt.Rows.Remove(removelist[i]);
            }

            //return dt;
        }
    }
}
